package approve.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import approve.action.LoginActivity;
import approve.adapter.Adapter_ListItem_Library;
import approve.model.Approvedoc;
import approve.model.ListLabel;
import models.util.MySqlDataAccessHelper;

public class ListLabelDAO {

	public ArrayList<ListLabel> getAllListLabel(String db,String idem){
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		ArrayList<ListLabel> lst = new ArrayList<ListLabel>();
		try{
			mysql.open(db);
			String sql="SELECT IDLable,LableName FROM list_lable la WHERE la.IDEmployee='" +  idem+ "'";
			ResultSet rs=mysql.executeQuery(sql);
			while(rs.next()){
				lst.add(new ListLabel(rs.getInt("IDLable"),rs.getString("LableName")));
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}

	//	public ArrayList<ListLabel> getCountAllListLabel(String db,String idem){
	//		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
	//		ArrayList<ListLabel> lst = new ArrayList<ListLabel>();
	//		try{
	//			mysql.open(db);
	//			int rows=0;
	//			String sql = "UPDATE listapprove SET IDLabel = IDStatus WHERE IDStatus > 10";
	//			rows=mysql.executeUpdate(sql);
	//			if(rows>=1){
	//				sql = "UPDATE listapprove l SET l.IDStatus=(SELECT a.IDStatus FROM approvedoc a WHERE a.IDGroupApprove=l.IDGroupApprove) WHERE l.IDStatus > 10";
	//				mysql.executeUpdate(sql);		
	//			}
	//			
	//			sql="SELECT IDLable,LableName FROM list_lable la WHERE la.IDEmployee='" +  idem+ "'";
	//			ResultSet rs=mysql.executeQuery(sql);
	//			while(rs.next()){
	//				int count=0;
	//				String sql1="select count(ap.IDApproveDoc) as count1 from approvedoc ap where ap.IDLabel="+rs.getInt("IDLable") +" group by ap.IDLabel";
	//				ResultSet rs1=mysql.executeQuery(sql1);
	//				while(rs1.next()){
	//					count+=rs1.getInt("count1");
	//				}
	//				sql1="select count(ap.IDApproveDoc) as count2 from listapprove l, approvedoc ap where ap.IDGroupApprove=l.IDGroupApprove and l.IDEmployee='"+idem+"' and l.IDLabel="+rs.getInt("IDLable")+" group by l.IDLabel";
	//				rs1=mysql.executeQuery(sql1);
	//				System.out.println(sql1);
	//				while(rs1.next()){
	//					count+=rs1.getInt("count2");
	//				}
	//				sql1="select count(ap.IDApproveDoc) as count3 from list_reference l, approvedoc ap where ap.IDGroupApprove=l.id_group and l.id_employee='"+idem+"' and l.IDLabel="+rs.getInt("IDLable")+" group by l.IDLabel";
	//				rs1=mysql.executeQuery(sql1);
	//				while(rs1.next()){
	//					count+=rs1.getInt("count3");
	//				}
	//				lst.add(new ListLabel(rs.getInt("IDLable"),rs.getString("LableName"),count));
	//			}
	//			rs.close();
	//		}catch(Exception e){
	//			e.printStackTrace();
	//		}finally{
	//			mysql.close();
	//		}
	//		return lst;
	//	}

	public int moveclick(String db,int idla, String strid,String idem){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="UPDATE listapprove SET IDLabel=" + idla+ " WHERE IDGroupApprove IN ("+strid+") and IDEmployee ='" + idem + "'";
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}

	public int addlabel(String db, String name,String idem){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="insert into list_lable(LableName,IDEmployee) values('"+name+"','"+idem+"')";
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}

	public int moverefer(String db,String idem,int idla,String stridgroup){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			String sql="UPDATE list_reference SET IDLabel=" + idla + " WHERE id_status = 1 AND id_group IN ("+stridgroup+") and id_employee ='" + idem+ "'";
			mysql.open(db);
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}

	public int moveMyApproveDoc(String db,String idem,int idla,String stridappdoc){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			String sql="UPDATE approvedoc SET IDLabel=" + idla+ " WHERE IDApproveDoc IN ("+stridappdoc+") AND IDEmployee ='" +idem + "'";
			mysql.open(db);
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}

	public int deleteLabel(String db,int idLabel) throws SQLException{
		int a = -1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "delete from list_lable where IDLable = "+idLabel;
		try {
			mysql.open(db);
			a=mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return a;
	}

	public int updateLabel(String db,int idLabel,String name) throws SQLException{
		int a = -1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "UPDATE list_lable SET LableName ='"+name+"' WHERE IDLable ="+idLabel;
		try {
			mysql.open(db);
			a=mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return a;
	}

	public static int getAllDocByLabel(String db,String idEmp, int label) {
		int kq=0;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		CallableStatement cs = null;
		CallableStatement cs2 = null;
		CallableStatement cs3 = null;
		String sql = "call sp_getAllDocByLabel(?,?)";
		String sql2 = "call sp_getReferDocByLabel(?,?)";
		String sql3 = "call sp_getApproveDocByLabel(?,?)";
		try {
			mysql.open(db);

			//---Update old ListApprove Label-----------
			int rows=0;
			String sqlOld1 = "UPDATE listapprove SET IDLabel = IDStatus WHERE IDStatus > 10";
			rows=mysql.executeUpdate(sqlOld1);
			if(rows>=1){
				String sqlOld2 = "UPDATE listapprove l SET l.IDStatus=(SELECT a.IDStatus FROM approvedoc a WHERE a.IDGroupApprove=l.IDGroupApprove) WHERE l.IDStatus > 10";
				mysql.executeUpdate(sqlOld2);		
			}

			cs = mysql.getConnection().prepareCall(sql);
			cs.setString(1, idEmp);
			cs.setInt(2, label);
			ResultSet rs = cs.executeQuery();

			cs2 = mysql.getConnection().prepareCall(sql2);
			cs2.setString(1, idEmp);
			cs2.setInt(2, label);
			ResultSet rs2 = cs2.executeQuery();

			cs3 = mysql.getConnection().prepareCall(sql3);
			cs3.setString(1, idEmp);
			cs3.setInt(2, label);
			ResultSet rs3 = cs3.executeQuery();

			while (rs.next()) {
				if (rs.getInt("IDTypeDoc") != 11) {
					kq++;
				}
			}				

			while (rs2.next()) {
				if (rs2.getInt("IDTypeDoc") != 11) {
					kq++;
				}
			}				

			while (rs3.next()) {
				if (rs3.getInt("IDTypeDoc") != 11) {
					kq++;
				}
			}				
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mysql.close();
		}
		return kq;
	}

	public static void moveLibrary(Approvedoc approvedoc,String database,int label){
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try {
			helper.open(database);
			System.out.println(" list check == " + Adapter_ListItem_Library.getListcheck().size());
			for (int i = 0; i < Adapter_ListItem_Library.getListcheck().size(); i++) {
				approvedoc = (Approvedoc)Adapter_ListItem_Library.getListcheck().get(i);
				if(approvedoc.getiDEmployee().equals(LoginActivity.emp.getIdemployee()) && approvedoc.getIdLabel() > 0){
					String sql1 = "UPDATE approvedoc SET IDLabel=" + label 
					+ " WHERE IDApproveDoc ='" + approvedoc.getiDApproveDoc() + "' AND IDEmployee ='" + LoginActivity.emp.getIdemployee() + "'";
					helper.executeUpdate(sql1);

				}else{
					String sql2 = "UPDATE listapprove SET IDLabel=" + label 
					+ " WHERE IDGroupApprove ='" + approvedoc.getiDGroupApprove() + "' AND IDEmployee ='" + LoginActivity.emp.getIdemployee() + "'";
					helper.executeUpdate(sql2);

					String sql3 = "UPDATE list_reference SET IDLabel=" + label
					+ " WHERE id_group ='" + approvedoc.getiDGroupApprove() + "' AND id_employee ='" + LoginActivity.emp.getIdemployee() + "'";
					helper.executeUpdate(sql3);
				}
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			helper.close();
		}
	}
}
